package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dto.AttnDto;

public class AttnDaoImp extends ViewListDao implements AttnDao {
	protected final String[] sortKeyList = new String[]{"MEMBERID","REALNAME","USERID","ADRRESS","CONTACTOWNER","CONTACTPHONE","VIRTUALMARGIN","MEMSTATUS"};//会员列表
	protected AttnDaoImp() throws TmsDaoException {
		super();
		// TODO Auto-generated constructor stub
	}

	protected AttnDaoImp(int pageNo,
			int maxIndex,int sortKey,boolean asc) throws TmsDaoException{
		super( pageNo,maxIndex, sortKey, asc);
	}
	
	
	@Override
	public List<AttnDto> getAttnInfo() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		AttnDto dto = null;
		List<AttnDto> list = new ArrayList<AttnDto>();

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT 			");
			sql.append("ATTNID,			");
			sql.append("MEMBER.REALNAME AS COMPANYNAME,    ");
			sql.append("ATTNPHOTO,     	");
			sql.append("ATTNNAME     	");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_ATTN);
			sql.append(" ATTN ");
			sql.append(" INNER JOIN  ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" MEMBER ");
			sql.append(" ON MEMBER.MEMBERID = ATTN.MEMBERID ");

			ps = conn.prepareStatement(sql.toString());

			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new AttnDto();
				dto.setAttnId(rs.getString("ATTNID"));
				dto.setCompanyName(rs.getString("COMPANYNAME"));
				dto.setAttnPhoto(rs.getString("ATTNPHOTO"));
				dto.setAttnName(rs.getString("ATTNNAME"));
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return list;
	}

	@Override
	public AttnDto getAttnInfoById(String attnId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		AttnDto dto = null;
		try {
			conn = super.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT 			");
			sql.append("ATTNPHOTO,     	");
			sql.append("CARDSCORE,     	");
			sql.append("ATTNNAME ,    	");
			sql.append("ACCESSCARDNO ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_ATTN);
			sql.append(" where ATTNID = '"+attnId+"' ");
			ps = conn.prepareStatement(sql.toString());
			// 执行sql
			rs = ps.executeQuery();
			while (rs.next()) {
				dto = new AttnDto();
				dto.setAttnId(attnId);
				dto.setCardScore(rs.getInt("CARDSCORE"));
				dto.setAttnPhoto(rs.getString("ATTNPHOTO"));
				dto.setAttnName(rs.getString("ATTNNAME"));
				dto.setAccessCardNo(rs.getString("ACCESSCARDNO"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
		return dto;
	}

	@Override
	public void insertAttn(AttnDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
	
		try {
			StringBuffer sql = new StringBuffer("INSERT INTO ");
			sql.append(TmsTableNames.TMS_ATTN);
			sql.append("(");
			sql.append("ATTNID,");
			sql.append("MEMBERID,");
			sql.append("ATTNNAME,");
			sql.append("ATTNIDCARDNO,");
			sql.append("ATTNADDRESS,");
			sql.append("ATTNPHONE,");
			sql.append("ATTNPHOTO,");
			sql.append("ACCESSCARDID,");
			sql.append("USERID,");
			sql.append("INSERTUSERID,");
			sql.append("UPDATEUSERID,");
			sql.append("INSERTTIME,");
			sql.append("UPDATETIME,");
			sql.append("ACCESSCARDNO");
			sql.append("  ) VALUES ( ");
			sql.append("?,?,?,?,?,?,?,?,?,?,?,SYSDATE(),SYSDATE(),?)");
			
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getAttnId());
			ps.setString(2, dto.getMemberId());
			ps.setString(3, dto.getAttnName());
			ps.setString(4, dto.getAttnIdCardNo());
			ps.setString(5, dto.getAttnAddress());
			ps.setString(6, dto.getAttnPhone());
			ps.setString(7, dto.getAttnPhoto());
			ps.setString(8, dto.getAccessCardId());
			ps.setString(9, dto.getUserId());
			ps.setInt(10, dto.getInsertUserId());
			ps.setInt(11, dto.getUpdateUserId());
			ps.setString(12, dto.getAccessCardNo());
			
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
	}

	
	
	@Override
	public AttnDto getIntegralInfo(String accessCardNo) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		AttnDto dto = null;
		try {
			conn = super.getConnection();
			
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("ATTN.ATTNID AS ATTNID,");
			sql.append("ATTN.ACCESSCARDID AS ACCESSCARDID,");
			sql.append("ATTN.ATTNNAME AS ATTNNAME,");
			sql.append("ATTN.ATTNIDCARDNO AS ATTNIDCARDNO,");
			sql.append("ATTN.ATTNPHONE AS ATTNPHONE,");
			sql.append("ATTN.ATTNPHOTO AS ATTNPHOTO,");
			sql.append("ATTN.CARDSCORE AS CARDSCORE,");
			//add by huangheli at 20151012 start
			sql.append("ATTN.INTEGRALRATIO AS INTEGRALRATIO,");
			sql.append("ATTN.VIRTUALMARGIN AS VIRTUALMARGIN,     ");
			//add by huangheli at 20151012 end
			sql.append("ATTN.REALMARGIN AS REALMARGIN,     ");
			sql.append("USER.FLAG AS USERID ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_ATTN);
			sql.append(" ATTN ");
			sql.append(" INNER JOIN ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" USER ");
			sql.append(" ON ATTN.USERID = USER.USERID ");
			sql.append(" WHERE ");
			sql.append(" ATTN.ACCESSCARDNO=? ");
			
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, accessCardNo);
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new AttnDto();
				dto.setAttnId(rs.getString("ATTNID"));
				dto.setAttnName(rs.getString("ATTNNAME"));
				dto.setAttnIdCardNo(rs.getString("ATTNIDCARDNO"));
				dto.setAttnPhone(rs.getString("ATTNPHONE"));
				dto.setAttnPhoto(rs.getString("ATTNPHOTO"));
				dto.setUserId(rs.getString("USERID"));
				dto.setAccessCardId(rs.getString("ACCESSCARDID"));
				dto.setVirtualMargin(rs.getInt("VIRTUALMARGIN"));
				dto.setCardScore(rs.getInt("CARDSCORE")); 
				//add by huangheli at 20151012 start
				dto.setIntegralRatio(rs.getDouble("INTEGRALRATIO"));
				//add by huangheli at 20151012 end
				dto.setRealMargin(rs.getInt("REALMARGIN"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return dto;
	}

	@Override
	public AttnDto getIntegralManagementInfo(String accId)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		AttnDto dto = null;
		try {
			conn = super.getConnection();
			
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("ATTN.ATTNID AS ATTNID,");
			sql.append("ATTN.ACCESSCARDID AS ACCESSCARDID,");
			sql.append("ATTN.ATTNNAME AS ATTNNAME,");
			sql.append("ATTN.ATTNIDCARDNO AS ATTNIDCARDNO,");
			sql.append("ATTN.ATTNPHONE AS ATTNPHONE,");
			sql.append("ATTN.ATTNPHOTO AS ATTNPHOTO,");
			sql.append("ATTN.CARDSCORE AS CARDSCORE,");
			//add by huangheli at 20151012 start
			sql.append("ATTN.INTEGRALRATIO AS INTEGRALRATIO,");
			//add by huangheli at 20151012 end
			sql.append("MEMBER.REALNAME AS MEMBERNAME,");
			sql.append("USER.FLAG AS USERID ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_ATTN);
			sql.append(" ATTN ");
			sql.append(" INNER JOIN ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" USER ");
			sql.append(" ON ATTN.USERID = USER.USERID ");
			sql.append(" INNER JOIN ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" MEMBER ");
			sql.append(" ON MEMBER.MEMBERID = ATTN.MEMBERID ");
			sql.append(" WHERE ");
			sql.append(" ATTN.ACCESSCARDNO=? ");
			
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, accId);
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new AttnDto();
				dto.setAttnId(rs.getString("ATTNID"));
				dto.setAttnName(rs.getString("ATTNNAME"));
				dto.setAttnIdCardNo(rs.getString("ATTNIDCARDNO"));
				dto.setAttnPhone(rs.getString("ATTNPHONE"));
				dto.setAttnPhoto(rs.getString("ATTNPHOTO"));
				dto.setUserId(rs.getString("USERID"));
				dto.setMemberId(rs.getString("MEMBERNAME"));
				dto.setAccessCardId(rs.getString("ACCESSCARDID")); 
				dto.setCardScore(rs.getInt("CARDSCORE")); 
				//add by huangheli at 20151012 start
				dto.setIntegralRatio(rs.getDouble("INTEGRALRATIO"));
				//add by huangheli at 20151012 end
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return dto;
	}

	@Override
	public void updateIntegralCardScore(AttnDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(TmsTableNames.TMS_ATTN);
		sql.append(" SET ");
		sql.append("CARDSCORE=?, ");
		sql.append("UPDATEUSERID=?, ");
		sql.append("UPDATETIME=SYSDATE() ");
		sql.append(" WHERE  ");
		sql.append(" ATTNID=? ");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, dto.getCardScore());
			ps.setInt(2, dto.getUpdateUserId());
			ps.setString(3, dto.getAttnId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
	}

	@Override
	public int findAttnByAccessCardNo(String accessCardNo)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		int flag = 1;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		sql.append("ATTNID ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_ATTN);
		sql.append(" WHERE  ");
		sql.append(" ACCESSCARDNO=? ");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, accessCardNo);
			rs = ps.executeQuery();
			while(rs.next()){
				flag =2;
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return flag;
	}
	 
	@Override
	public int findAttnByAccessCardId(String accessCardId)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		int flag = 1;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		sql.append("ATTNID ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_ATTN);
		sql.append(" WHERE  ");
		sql.append(" ACCESSCARDID=? ");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, accessCardId);
			rs = ps.executeQuery();
			while(rs.next()){
				flag =2;
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return flag;
	}

	@Override
	public List<AttnDto> findAttnList() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		AttnDto dto = null;
		List<AttnDto>  list = new ArrayList<AttnDto>();
		
		try {
			conn = super.getConnection();
		
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("ATTN.ATTNID  AS ATTNID 	,");
			sql.append("ATTN.ATTNNAME  AS ATTNNAME 	,");
			sql.append("ATTN.USERID AS USERID,     ");
			sql.append("ATTN.ATTNIDCARDNO AS ATTNIDCARDNO,     ");
			sql.append("ATTN.ATTNPHONE AS ATTNPHONE,     ");
			sql.append("ATTN.ATTNPHOTO AS ATTNPHOTO,     ");
			sql.append("ATTN.MEMBERID  AS MEMBERID ,   ");
			sql.append("USER.FLAG as MEMSTATUS     ");//与user表关联取出帐号状态
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_ATTN);
			sql.append(" AS ATTN    ");
			sql.append(" LEFT JOIN    ");
			sql.append(TmsTableNames.TMS_USER    );
			sql.append(" AS USER      ");
			sql.append(" ON USER.USERID = ATTN.USERID    ");
			sql.append(" WHERE USER.FLAG<>2  ");
			//排序方法
			sql.append(getOrderBySql(sortKeyList));
			
			ps = conn.prepareStatement(sql.toString());
			
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new AttnDto();
				dto.setMemberId(rs.getString("MEMBERID"));
				dto.setAttnId(rs.getString("ATTNID"));
				dto.setUserId(rs.getString("USERID"));
				dto.setAttnName(rs.getString("ATTNNAME"));
				dto.setAttnIdCardNo(rs.getString("ATTNIDCARDNO"));
				dto.setAttnPhone(rs.getString("ATTNPHONE"));
				dto.setAttnPhoto(rs.getString("ATTNPHOTO"));
				dto.setAttStatus(rs.getInt("MEMSTATUS"));
				
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return list;
	}

	
	public List<AttnDto> findAllAttn()throws TmsDaoException{
		List<AttnDto> list = new ArrayList<AttnDto>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		sql.append("ATTNID,");
		sql.append("MEMBERID,");
		sql.append("ATTNNAME,");
		sql.append("ATTNIDCARDNO,");
		sql.append("ATTNADDRESS,");
		sql.append("ATTNPHONE,");
		sql.append("ATTNPHOTO,");
		sql.append("ACCESSCARDID,");
		sql.append("USERID,");
		sql.append("ACCESSCARDNO,");
		sql.append("INTEGRALRATIO,");
		sql.append("CARDSCORE,");
		sql.append("VIRTUALMARGIN,");
		sql.append("shortName");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_ATTN);
		
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while(rs.next()){
				AttnDto dto = new AttnDto();
				dto.setAttnId(rs.getString("ATTNID"));
				dto.setMemberId(rs.getString("MEMBERID"));
				dto.setAttnName(rs.getString("ATTNNAME"));
				dto.setAttnIdCardNo(rs.getString("ATTNIDCARDNO"));
				dto.setAttnAddress(rs.getString("ATTNADDRESS"));
				dto.setAttnPhone(rs.getString("ATTNPHONE"));
				dto.setAttnPhoto(rs.getString("ATTNPHOTO"));
				dto.setAccessCardId(rs.getString("ACCESSCARDID"));
				dto.setAccessCardNo(rs.getString("ACCESSCARDNO"));
				dto.setIntegralRatio(rs.getDouble("INTEGRALRATIO"));
				dto.setUserId(rs.getString("USERID"));
				dto.setCardScore(rs.getInt("CARDSCORE"));
				dto.setVirtualMargin(rs.getInt("VIRTUALMARGIN"));
				dto.setShortName(rs.getString("shortName"));
				list.add(dto);
			}
		}catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return list;
	}
	
	@Override
	public AttnDto findAttnByAttnId(String attnId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		AttnDto dto = new AttnDto();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		sql.append("ATTNID,");
		sql.append("MEMBERID,");
		sql.append("ATTNNAME,");
		sql.append("ATTNIDCARDNO,");
		sql.append("ATTNADDRESS,");
		sql.append("ATTNPHONE,");
		sql.append("ATTNPHOTO,");
		sql.append("ACCESSCARDID,");
		sql.append("USERID,");
		sql.append("ACCESSCARDNO,");
		sql.append("INTEGRALRATIO,");
		sql.append("CARDSCORE,");
		sql.append("VIRTUALMARGIN,");
		sql.append("shortName");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_ATTN);
		sql.append(" WHERE  ");
		sql.append(" ATTNID=? ");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, attnId);
			rs = ps.executeQuery();
			while(rs.next()){
				dto.setAttnId(rs.getString("ATTNID"));
				dto.setMemberId(rs.getString("MEMBERID"));
				dto.setAttnName(rs.getString("ATTNNAME"));
				dto.setAttnIdCardNo(rs.getString("ATTNIDCARDNO"));
				dto.setAttnAddress(rs.getString("ATTNADDRESS"));
				dto.setAttnPhone(rs.getString("ATTNPHONE"));
				dto.setAttnPhoto(rs.getString("ATTNPHOTO"));
				dto.setAccessCardId(rs.getString("ACCESSCARDID"));
				dto.setAccessCardNo(rs.getString("ACCESSCARDNO"));
				dto.setIntegralRatio(rs.getDouble("INTEGRALRATIO"));
				dto.setUserId(rs.getString("USERID"));
				dto.setCardScore(rs.getInt("CARDSCORE"));
				dto.setVirtualMargin(rs.getInt("VIRTUALMARGIN"));
				dto.setShortName(rs.getString("shortName"));
			}
		}catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return dto;
	}
	
	@Override
	public void updateAttnMarginInfo(AttnDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(TmsTableNames.TMS_ATTN);
		sql.append(" SET ");
		sql.append(" VIRTUALMARGIN=?, ");
		sql.append(" REALMARGIN=?, ");
		sql.append(" GIFTMARGIN=?, ");
		sql.append(" UPDATEUSERID=?, ");
		sql.append(" UPDATETIME=SYSDATE() ");
		sql.append(" WHERE  ");
		sql.append(" ACCESSCARDNO=? ");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, dto.getVirtualMargin());
			ps.setInt(2, dto.getRealMargin());
			ps.setInt(3, dto.getGiftMargin());
			ps.setInt(4, dto.getUpdateUserId());
			ps.setString(5, dto.getAccessCardNo());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs,ps);
		}
	}

	@Override
	public void updateAttnVirtualMargin(int transferPrice, String attnId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(TmsTableNames.TMS_ATTN);
		sql.append(" SET ");
		sql.append(" VIRTUALMARGIN=VIRTUALMARGIN - ?, ");
		sql.append(" UPDATETIME=SYSDATE() ");
		sql.append(" WHERE  ");
		sql.append(" attnId=? ");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1,transferPrice);
			ps.setString(2, attnId);
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs,ps);
		}
	}

	

	@Override
	public void updateAttn(AttnDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(TmsTableNames.TMS_ATTN);
		sql.append(" SET ");
		sql.append("MEMBERID=?, ");
		sql.append("ATTNNAME=?, ");
		sql.append("ATTNIDCARDNO=?, ");
		sql.append("ATTNADDRESS=?, ");
		sql.append("ATTNPHONE=?, ");
		sql.append("ATTNPHOTO=?, ");
		sql.append("UPDATEUSERID=?, ");
		sql.append("UPDATETIME=SYSDATE() ");
		sql.append(" WHERE  ");
		sql.append(" ATTNID=? ");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getMemberId());
			ps.setString(2, dto.getAttnName());
			ps.setString(3, dto.getAttnIdCardNo());
			ps.setString(4, dto.getAttnAddress());
			ps.setString(5, dto.getAttnPhone());
			ps.setString(6, dto.getAttnPhoto());
			ps.setInt(7, dto.getUpdateUserId());
			ps.setString(8, dto.getAttnId());
			
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		
	}

	@Override
	public void updateIntegralcard(AttnDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(TmsTableNames.TMS_ATTN);
		sql.append(" SET ");
		sql.append("ACCESSCARDID=?, ");
		sql.append("ACCESSCARDNO=?, ");
		sql.append("UPDATEUSERID=?, ");
		sql.append("UPDATETIME=SYSDATE() ");
		sql.append(" WHERE  ");
		sql.append(" ATTNID=? ");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getAccessCardId());
			ps.setString(2, dto.getAccessCardNo());
			ps.setInt(3, dto.getUpdateUserId());
			ps.setString(4, dto.getAttnId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
	}

	@Override
	public AttnDto findAttnByUserId(String userId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		AttnDto dto = new AttnDto();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		sql.append("ATTNID,");
		sql.append("MEMBERID,");
		sql.append("ATTNNAME,");
		sql.append("ATTNIDCARDNO,");
		sql.append("ATTNADDRESS,");
		sql.append("ATTNPHONE,");
		sql.append("ATTNPHOTO,");
		sql.append("ACCESSCARDID,");
		sql.append("USERID,");
		sql.append("ACCESSCARDNO,");
		sql.append("CARDSCORE");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_ATTN);
		sql.append(" WHERE  ");
		sql.append(" USERID=? ");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, userId);
			rs = ps.executeQuery();
			while(rs.next()){
				dto.setAttnId(rs.getString("ATTNID"));
				dto.setMemberId(rs.getString("MEMBERID"));
				dto.setAttnName(rs.getString("ATTNNAME"));
				dto.setAttnIdCardNo(rs.getString("ATTNIDCARDNO"));
				dto.setAttnAddress(rs.getString("ATTNADDRESS"));
				dto.setAttnPhone(rs.getString("ATTNPHONE"));
				dto.setAttnPhoto(rs.getString("ATTNPHOTO"));
				dto.setAccessCardId(rs.getString("ACCESSCARDID"));
				dto.setAccessCardNo(rs.getString("ACCESSCARDNO"));
				dto.setUserId(rs.getString("USERID"));
				dto.setCardScore(rs.getInt("CARDSCORE"));
			}
		}catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return dto;
	}
	public void updateMarginInfoByAccessCardNo(AttnDto dto)throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(TmsTableNames.TMS_ATTN);
		sql.append(" SET ");
		sql.append(" VIRTUALMARGIN=?, ");
		sql.append(" REALMARGIN=?, ");
		sql.append(" UPDATEUSERID=?, ");
		sql.append(" recharge=?,");
		sql.append(" rechargeTime=SYSDATE(), ");
		sql.append(" UPDATETIME=SYSDATE() ");
		sql.append(" WHERE  ");
		sql.append(" ACCESSCARDNO=? ");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, dto.getVirtualMargin());
			ps.setInt(2, dto.getRealMargin());
			ps.setInt(3, dto.getUpdateUserId());
			ps.setInt(4, dto.getRecharge());
			ps.setString(5, dto.getAccessCardNo());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs,ps);
		}
	}
	
	@Override
	public List<AttnDto> findAttnList(String memberId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		sql.append("ATTNID,");
		sql.append("MEMBERID,");
		sql.append("ATTNNAME,");
		sql.append("ATTNIDCARDNO,");
		sql.append("ATTNADDRESS,");
		sql.append("ATTNPHONE,");
		sql.append("ATTNPHOTO,");
		sql.append("ACCESSCARDID,");
		sql.append("USERID,");
		sql.append("ACCESSCARDNO,");
		sql.append("INTEGRALRATIO,");
		sql.append("CARDSCORE,");
		sql.append("VIRTUALMARGIN,");
		sql.append("shortName");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_ATTN);
		sql.append(" WHERE  VIRTUALMARGIN <> 0");
		boolean flag = false;
		if(memberId != null && memberId.trim().length() > 0) {
			String type = memberId.substring(0, 1);
			if(type.equals("4")) {
				flag = true;
				sql.append(" and ATTNID=? ");
			} else if (type.equals("5")) {
				flag = true;
				sql.append(" and MEMBERID=? ");
			}
		}
		sql.append(" order by ATTNID ");
		
		List<AttnDto> list = new ArrayList<AttnDto>();
		try {
			ps = conn.prepareStatement(sql.toString());
			if(flag && memberId != null && memberId.trim().length() > 0)  ps.setString(1, memberId);
			rs = ps.executeQuery();
			while(rs.next()){
				AttnDto dto = new AttnDto();
				dto.setAttnId(rs.getString("ATTNID"));
				dto.setMemberId(rs.getString("MEMBERID"));
				dto.setAttnName(rs.getString("ATTNNAME"));
				dto.setAttnIdCardNo(rs.getString("ATTNIDCARDNO"));
				dto.setAttnAddress(rs.getString("ATTNADDRESS"));
				dto.setAttnPhone(rs.getString("ATTNPHONE"));
				dto.setAttnPhoto(rs.getString("ATTNPHOTO"));
				dto.setAccessCardId(rs.getString("ACCESSCARDID"));
				dto.setAccessCardNo(rs.getString("ACCESSCARDNO"));
				dto.setIntegralRatio(rs.getDouble("INTEGRALRATIO"));
				dto.setUserId(rs.getString("USERID"));
				dto.setCardScore(rs.getInt("CARDSCORE"));
				dto.setVirtualMargin(rs.getInt("VIRTUALMARGIN"));
				dto.setShortName(rs.getString("shortName"));
				list.add(dto);
			}
		}catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return list;
	}
}
